﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Volume_Resource_Management
{
    class databaseAccessability
    {

        #region Main Form

        public static List<int> getButtonLocation(int storeNo, int HFB)
        {
            dataConnectionDataContext vc = new dataConnectionDataContext();
            List<int> toReturn = new List<int>();

            switch (HFB)
            {
                case 0:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB1_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB1_Y);

                    break;

                case 1:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB2_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB2_Y);

                    break;

                case 2:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB3_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB3_Y);

                    break;

                case 3:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB4_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB4_Y);

                    break;

                case 5:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB6_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB6_Y);

                    break;

                case 6:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB7_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB7_Y);

                    break;

                case 7:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB8_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB8_Y);

                    break;

                case 8:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB9_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB9_Y);

                    break;

                case 9:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB10_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB10_Y);

                    break;

                case 10:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB11_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB11_Y);

                    break;

                case 11:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB12_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB12_Y);

                    break;

                case 12:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB13_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB13_Y);

                    break;

                case 13:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB14_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB14_Y);

                    break;

                case 14:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB15_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB15_Y);

                    break;

                case 15:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB16_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB16_Y);

                    break;

                case 16:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB17_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB17_Y);

                    break;

                case 17:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB18_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB18_Y);

                    break;

                case 18:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB19_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB19_Y);

                    break;

                case 19:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB92_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().HFB92_Y);

                    break;

                case 20:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().SS_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().SS_Y);

                    break;

                case 21:
                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().FS_X);

                    toReturn.Add((from sL in vc.GetTable<systemLocation>()
                                  where (sL.storeNo == storeNo)
                                  select sL).SingleOrDefault<systemLocation>().FS_Y);

                    break;

                default:
                    toReturn.Add(20);
                    toReturn.Add(100);
                    break;
            }

            return toReturn;
        }

            public static void updateLocation(int storeNo, int xLoc, int yLoc, int HFB)
            {
                dataConnectionDataContext vc = new dataConnectionDataContext();

                var query =
                    from loc in vc.systemLocations
                    where loc.storeNo == storeNo
                    select loc;

                // Execute the query, and change the column values
                // you want to change.
                foreach (systemLocation sl in query)
                {
                    if (HFB == 0)
                    {
                        sl.HFB1_X = xLoc;
                        sl.HFB1_Y = yLoc;
                    }
                    else if (HFB == 1)
                    {
                        sl.HFB2_X = xLoc;
                        sl.HFB2_Y = yLoc;
                    }
                    else if (HFB == 2)
                    {
                        sl.HFB3_X = xLoc;
                        sl.HFB3_Y = yLoc;
                    }
                    else if (HFB == 3)
                    {
                        sl.HFB4_X = xLoc;
                        sl.HFB4_Y = yLoc;
                    }
                    else if (HFB == 5)
                    {
                        sl.HFB6_X = xLoc;
                        sl.HFB6_Y = yLoc;
                    }
                    else if (HFB == 6)
                    {
                        sl.HFB7_X = xLoc;
                        sl.HFB7_Y = yLoc;
                    }
                    else if (HFB == 7)
                    {
                        sl.HFB8_X = xLoc;
                        sl.HFB8_Y = yLoc;
                    }
                    else if (HFB == 8)
                    {
                        sl.HFB9_X = xLoc;
                        sl.HFB9_Y = yLoc;
                    }
                    else if (HFB == 9)
                    {
                        sl.HFB10_X = xLoc;
                        sl.HFB10_Y = yLoc;
                    }
                    else if (HFB == 10)
                    {
                        sl.HFB11_X = xLoc;
                        sl.HFB11_Y = yLoc;
                    }
                    else if (HFB == 11)
                    {
                        sl.HFB12_X = xLoc;
                        sl.HFB12_Y = yLoc;
                    }
                    else if (HFB == 12)
                    {
                        sl.HFB13_X = xLoc;
                        sl.HFB13_Y = yLoc;
                    }
                    else if (HFB == 13)
                    {
                        sl.HFB14_X = xLoc;
                        sl.HFB14_Y = yLoc;
                    }
                    else if (HFB == 14)
                    {
                        sl.HFB15_X = xLoc;
                        sl.HFB15_Y = yLoc;
                    }
                    else if (HFB == 15)
                    {
                        sl.HFB16_X = xLoc;
                        sl.HFB16_Y = yLoc;
                    }
                    else if (HFB == 16)
                    {
                        sl.HFB17_X = xLoc;
                        sl.HFB17_Y = yLoc;
                    }
                    else if (HFB == 17)
                    {
                        sl.HFB18_X = xLoc;
                        sl.HFB18_Y = yLoc;
                    }
                    else if (HFB == 18)
                    {
                        sl.HFB19_X = xLoc;
                        sl.HFB19_Y = yLoc;
                    }
                    else if (HFB == 19)
                    {
                        sl.HFB92_X = xLoc;
                        sl.HFB92_Y = yLoc;
                    }
                    else if (HFB == 20)
                    {
                        sl.SS_X = xLoc;
                        sl.SS_Y = yLoc;
                    }
                    else if (HFB == 21)
                    {
                        sl.FS_X = xLoc;
                        sl.FS_Y = yLoc;
                    }

                    // Submit the changes to the database.
                    try
                    {
                        vc.SubmitChanges();
                    }
                    catch (Exception e)
                    {
                        System.Windows.Forms.MessageBox.Show(e.ToString());
                    }
                }
            }

            public static List<string> getHoursAndLimit(int storeNumber, int weekNumber, int tertial, int year)
            {
                dataConnectionDataContext dc = new dataConnectionDataContext();

                List<string> toReturn = new List<string>();

                var query = (from sG in dc.GetTable<salesGoal>()
                             where (sG.storeNo == storeNumber) && (sG.weekNumber == weekNumber) && (sG.tertial == tertial) && (sG.FY == year)
                             select sG).Single();

                toReturn.Add(query.weeklyHours);
                toReturn.Add(query.holidayLimit);
                
                return toReturn;
            }

            public static string getScenario(int storeNumber, int year)
            {
                dataConnectionDataContext dc = new dataConnectionDataContext();

                string toReturn;

                var query = (from sG in dc.GetTable<systemInformation>()
                             where (sG.storeNumber == storeNumber) && (sG.FY == year)
                             select sG).Single();

                toReturn = query.scenario.ToString();

                return toReturn;
            }

        #endregion

        #region First Load

            public static void newStoreWeekTargets(string storeNo, List<List<string>> info, frmFirstLoad fLoad, int year)
            {
                dataConnectionDataContext dA = new dataConnectionDataContext();

                for (int i = 0; i < info.Count; i++)
                {
                    salesGoal sG = new salesGoal();

                    sG.storeNo = Convert.ToInt32(storeNo);
                    sG.FY = year;
                    sG.weekNumber = Convert.ToInt16(info[i][0]);
                    sG.tertial = Convert.ToInt32(info[i][1]);
                    sG.Goal = Convert.ToInt32(info[i][2]);
                    sG.weeklyHours = info[i][3];
                    sG.actualHours = info[i][4];
                    sG.holidayLimit = info[i][5];

                    dA.salesGoals.InsertOnSubmit(sG);
                    dA.SubmitChanges();

                    fLoad.progressBar2.Invoke(new Action(() => fLoad.progressBar2.PerformStep()));
                }
            }

            public static void newStoreButLocs(List<int> locs)
            {
                dataConnectionDataContext dA = new dataConnectionDataContext();

                systemLocation sL = new systemLocation();

                sL.storeNo = locs[0];
                sL.HFB1_X = locs[1];
                sL.HFB1_Y = locs[2];
                sL.HFB2_X = locs[3];
                sL.HFB2_Y = locs[4];
                sL.HFB3_X = locs[5];
                sL.HFB3_Y = locs[6];
                sL.HFB4_X = locs[7];
                sL.HFB4_Y = locs[8];
                sL.HFB6_X = locs[9];
                sL.HFB6_Y = locs[10];
                sL.HFB7_X = locs[11];
                sL.HFB7_Y = locs[12];
                sL.HFB8_X = locs[13];
                sL.HFB8_Y = locs[14];
                sL.HFB9_X = locs[15];
                sL.HFB9_Y = locs[16];
                sL.HFB10_X = locs[17];
                sL.HFB10_Y = locs[18];
                sL.HFB11_X = locs[19];
                sL.HFB11_Y = locs[20];
                sL.HFB12_X = locs[21];
                sL.HFB12_Y = locs[22];
                sL.HFB13_X = locs[23];
                sL.HFB13_Y = locs[24];
                sL.HFB14_X = locs[25];
                sL.HFB14_Y = locs[26];
                sL.HFB15_X = locs[27];
                sL.HFB15_Y = locs[28];
                sL.HFB16_X = locs[29];
                sL.HFB16_Y = locs[30];
                sL.HFB17_X = locs[31];
                sL.HFB17_Y = locs[32];
                sL.HFB18_X = locs[33];
                sL.HFB18_Y = locs[34];
                sL.HFB19_X = locs[35];
                sL.HFB19_Y = locs[36];
                sL.HFB92_X = locs[37];
                sL.HFB92_Y = locs[38];
                sL.SS_X = locs[39];
                sL.SS_Y = locs[40];
                sL.FS_X = locs[41];
                sL.FS_Y = locs[42];


                dA.systemLocations.InsertOnSubmit(sL);
                dA.SubmitChanges();
            }

            public static void newStoreSalesInfo(string storeNo, frmFirstLoad load, int year)
            {
                dataConnectionDataContext dA = new dataConnectionDataContext();

                systemInformation sI = new systemInformation();

                sI.storeNumber = Convert.ToInt32(storeNo);
                sI.FY = year;
                sI.hours = 0;
                sI.cdShare = 0;
                sI.pricePerCube = 0;
                sI.productivityIndex = 0;
                sI.scenario = 100;
                sI.storeSalesGoal = 0;

                dA.systemInformations.InsertOnSubmit(sI);
                dA.SubmitChanges();

                load.progressBar2.Invoke(new Action(() => load.progressBar2.PerformStep()));

            }

            public static void insertHFB(string date, string loc, string sm, string hfb, string zeropal, string zerovol,
                string onepal, string onevol, string totpal, string totvol, frmFirstLoad load)
            {
                dataConnectionDataContext vc = new dataConnectionDataContext();
                GoodsFlowPlanning_HFB gfpHFB = new GoodsFlowPlanning_HFB();

                gfpHFB.Date = date;
                gfpHFB.Loc = loc;
                gfpHFB.SalesMethod = sm;
                gfpHFB.HFB = hfb;
                gfpHFB.DT_0_PAL = zeropal;
                gfpHFB.DT_0_VOL = zerovol;
                gfpHFB.DT_1_PAL = onepal;
                gfpHFB.DT_1_VOL = onevol;
                gfpHFB.TOT_PAL = totpal;
                gfpHFB.TOT_VOL = totvol;

                vc.GoodsFlowPlanning_HFBs.InsertOnSubmit(gfpHFB);
                vc.SubmitChanges();
                load.progressBar3.Invoke(new Action(() => load.progressBar3.PerformStep()));
            }

            public static void insertArticles(string loc, string hfb, string item, string itemDesc, string recDate, string sm, string orderUnit,
                string ult, string status, string recQ, string recV, frmFirstLoad load)
            {
                dataConnectionDataContext vc = new dataConnectionDataContext();
                ArticleStatus aS = new ArticleStatus();

                aS.Loc = loc;
                aS.HFB = hfb;
                aS.Item = item;
                aS.ItemDescr = itemDesc;
                aS.RecDate = recDate;
                aS.SalesMethod = sm;
                aS.OrderUnitType = orderUnit;
                aS.ULT = ult;
                aS.PlanStatus = status;
                aS.Rec_QTY = recQ;
                aS.Rec_VOL_M3 = recV;

                vc.ArticleStatus.InsertOnSubmit(aS);
                vc.SubmitChanges();
                load.progressBar4.Invoke(new Action(() => load.progressBar4.PerformStep()));
            }


        #endregion

        #region Daily Deliveries

        public static List<ArticleStatus> getArticles(string storeNo, string date, string hfb, string sm)
        {
            dataConnectionDataContext dc = new dataConnectionDataContext();

            var result = (from aS in dc.GetTable<ArticleStatus>()
                          where (aS.RecDate == date) && (aS.HFB == hfb) && (aS.SalesMethod == sm) && (aS.Loc == "STO" + storeNo)
                          select aS).ToList<ArticleStatus>();

            return result;
        }

            public static List<ArticleStatus> getArticles(string storeNo, string date, string sm)
        {
            dataConnectionDataContext dc = new dataConnectionDataContext();

            var result = (from aS in dc.GetTable<ArticleStatus>()
                          where (aS.RecDate == date) && (aS.SalesMethod == sm) && (aS.Loc == "STO" + storeNo)
                          select aS).ToList<ArticleStatus>();

            return result;
        }

        #endregion

        #region Splash Screen

            public static int getDBValues(int storeNo)
            {
                dataConnectionDataContext dc = new dataConnectionDataContext();

                int count = (from gfHFB in dc.GoodsFlowPlanning_HFBs
                             where (gfHFB.Loc == "STO" + storeNo)
                             select gfHFB).Count();

                return count;
            }

            public static List<double> readVolumes(string selDate, string HFB, frmSplash splsh)
            {
                string newHFB = "";
                dataConnectionDataContext dc = new dataConnectionDataContext();
                List<double> toReturn = new List<double>();

                if (HFB.Length == 1)
                    newHFB = "0" + HFB;
                else
                    newHFB = HFB;

                var query = (from gfHFB in dc.GetTable<GoodsFlowPlanning_HFB>()
                             where (gfHFB.Date == selDate) && (gfHFB.HFB == newHFB) &&
                                (gfHFB.SalesMethod == "0")
                             select gfHFB);

                foreach (GoodsFlowPlanning_HFB GF in query)
                {
                    toReturn.Add(Convert.ToDouble(GF.DT_0_PAL));
                    toReturn.Add(Convert.ToDouble(GF.DT_0_VOL));
                    toReturn.Add(Convert.ToDouble(GF.DT_1_PAL));
                    toReturn.Add(Convert.ToDouble(GF.DT_1_VOL));
                    toReturn.Add(Convert.ToDouble(GF.TOT_PAL));
                    toReturn.Add(Convert.ToDouble(GF.TOT_VOL));
                    splsh.progressBar1.Invoke(new Action(() => splsh.progressBar1.PerformStep()));
                }

                return toReturn;
            }

            public static List<double> readSM(string selDate, string sm)
            {
                dataConnectionDataContext vc = new dataConnectionDataContext();
                List<double> toReturn = new List<double>();

                double DT0PAL = 0, DT0VOL = 0, DT1PAL = 0, DT1VOL = 0, TOTPAL = 0, TOTVOL = 0;

                var query = (from gfHFB in vc.GetTable<GoodsFlowPlanning_HFB>()
                             where (gfHFB.Date == selDate) && (gfHFB.SalesMethod == sm)
                             select gfHFB);

                foreach (GoodsFlowPlanning_HFB GF in query)
                {
                    DT0PAL += Convert.ToDouble(GF.DT_0_PAL);
                    DT0VOL += Convert.ToDouble(GF.DT_0_VOL);
                    DT1PAL += Convert.ToDouble(GF.DT_1_PAL);
                    DT1VOL += Convert.ToDouble(GF.DT_1_VOL);
                    TOTPAL += Convert.ToDouble(GF.TOT_PAL);
                    TOTVOL += Convert.ToDouble(GF.TOT_VOL);
                }

                toReturn.Add(DT0PAL);
                toReturn.Add(DT0VOL);
                toReturn.Add(DT1PAL);
                toReturn.Add(DT1VOL);
                toReturn.Add(TOTPAL);
                toReturn.Add(TOTVOL);

                return toReturn;
            }

        #endregion

        #region Resource Plan

            public static void updateSalesInformation(int weekno, int tertial, int goal, int year)
        {
            dataConnectionDataContext dC = new dataConnectionDataContext();

            var query = from sG in dC.salesGoals
                        where (sG.weekNumber == weekno) && (sG.tertial == tertial) && (sG.FY == year)
                        select sG;

            foreach (salesGoal sG in query)
            {
                sG.Goal = goal;

                dC.SubmitChanges();
            }

            dC.Dispose();
        }

            public static List<int> getTertial(int storeNo, int tert, int year)
        {
            List<int> toReturn = new List<int>();

            dataConnectionDataContext dc = new dataConnectionDataContext();

            var query = (from ter in dc.GetTable<salesGoal>()
                         where (ter.tertial == tert) && (ter.storeNo == storeNo) && (ter.FY == year)
                         select ter);

            foreach (salesGoal sg in query)
            {
                toReturn.Add(sg.Goal);
            }

            dc.Dispose();

            return toReturn;
        }

            public static void createNextYearSystemInformation(int storeNumber, int year)
        {
            dataConnectionDataContext dA = new dataConnectionDataContext();

            systemInformation sI = new systemInformation();

            sI.storeNumber = storeNumber;
            sI.FY = year;
            sI.storeSalesGoal = 0;
            sI.cdShare = 0;
            sI.productivityIndex = 0;
            sI.pricePerCube = 0;
            sI.hours = 0;
            sI.scenario = 100;

            dA.systemInformations.InsertOnSubmit(sI);
            dA.SubmitChanges();

            dA.Dispose();
        }

            public static void createNextYear(int storeNumber, int year, int weekno, int tertial)
        {
            dataConnectionDataContext dA = new dataConnectionDataContext();

            salesGoal sG = new salesGoal();

            sG.storeNo = storeNumber;
            sG.FY = year;
            sG.weekNumber = weekno;
            sG.tertial = tertial;
            sG.Goal = 0;
            sG.weeklyHours = "0";
            sG.actualHours = "0";
            sG.holidayLimit = "0";

            dA.salesGoals.InsertOnSubmit(sG);
            dA.SubmitChanges();

            dA.Dispose();

        }

            public static int checkForNextYear(int storeNumber, int year)
        {
            dataConnectionDataContext dc = new dataConnectionDataContext();

            int count = (from sG in dc.salesGoals
                         where (sG.storeNo == storeNumber) && (sG.FY == year)
                         select sG).Count();

            dc.Dispose();

            return count;
        }

            public static List<string> getActualHours(int storeNumber, int tertial, int currentYear)
        {
            List<string> toReturn = new List<string>();

            dataConnectionDataContext dC = new dataConnectionDataContext();

            var query = (from sys in dC.GetTable<salesGoal>()
                         where (sys.storeNo == storeNumber) && (sys.tertial == tertial) && (sys.FY == currentYear)
                         select sys);

            foreach (salesGoal sI in query)
            {
                toReturn.Add(sI.actualHours.ToString());
            }

            dC.Dispose();

            return toReturn;
        }

            public static void updateWeeklyGoals(int storeNumber, int weekNumber, int tertial, int year, string hours, string actHours, string holidayLimit)
        {
            dataConnectionDataContext vc = new dataConnectionDataContext();

            var query =
                from sysInfo in vc.salesGoals
                where (sysInfo.storeNo == storeNumber) && (sysInfo.weekNumber == weekNumber) && (sysInfo.tertial == tertial) && (sysInfo.FY == year)
                select sysInfo;

            foreach (salesGoal sG in query)
            {
                sG.weeklyHours = hours;
                sG.actualHours = actHours;
                sG.holidayLimit = holidayLimit;
            }

            vc.SubmitChanges();

            vc.Dispose();
        }

            public static void updateSystemInformation(int storeNumber, int year, int storeGoal, int cdShare, double productivityIndex, int pricePerCube, 
                int hours, int scenario)
        {
            dataConnectionDataContext vc = new dataConnectionDataContext();

            var query =
                from sysInfo in vc.systemInformations
                where (sysInfo.storeNumber == storeNumber) && (sysInfo.FY == year)
                select sysInfo;

            foreach (systemInformation sys in query)
            {
                sys.storeSalesGoal = storeGoal;
                sys.pricePerCube = pricePerCube;
                sys.cdShare = cdShare;
                sys.productivityIndex = productivityIndex;
                sys.hours = hours;
                sys.scenario = scenario;
            }

            vc.SubmitChanges();
            vc.Dispose();
        }

            public static List<string> getOrigHours(int storeNumber, int tertial, int year)
        {
            List<string> toReturn = new List<string>();

            dataConnectionDataContext dC = new dataConnectionDataContext();

            var query = (from sys in dC.GetTable<salesGoal>()
                         where (sys.storeNo == storeNumber) && (sys.tertial == tertial) && (sys.FY == year)
                         select sys);

            foreach (salesGoal sI in query)
            {
               toReturn.Add(sI.weeklyHours);
            }

            dC.Dispose();

            return toReturn;
        }

            public static List<string> getSalesForcast(int storeNumber, int tertial, int year)
        {
            List<string> toReturn = new List<string>();

            dataConnectionDataContext dC = new dataConnectionDataContext();

            var query = (from sys in dC.GetTable<salesGoal>()
                         where (sys.storeNo == storeNumber) && (sys.tertial == tertial) && (sys.FY == year)
                         select sys);

            foreach (salesGoal sI in query)
            {
                toReturn.Add(sI.Goal.ToString());
            }

            dC.Dispose();

            return toReturn;
        }

            public static List<string> getSystemInformation(int storeNumber, int year)
            {
                List<string> toReturn = new List<string>();

                dataConnectionDataContext dC = new dataConnectionDataContext();

                var query = (from sys in dC.GetTable<systemInformation>()
                             where (sys.storeNumber == storeNumber) && (sys.FY == year)
                             select sys);

                foreach (systemInformation sI in query)
                {
                    toReturn.Add(sI.storeSalesGoal.ToString());
                    toReturn.Add(sI.cdShare.ToString());
                    toReturn.Add(sI.productivityIndex.ToString());
                    toReturn.Add(sI.pricePerCube.ToString());
                    toReturn.Add(sI.hours.ToString());
                    toReturn.Add(sI.scenario.ToString());
                }

                dC.Dispose();

                return toReturn;
            }

            public static List<string> getHolidayLimit(int storeNumber, int year, int tertial)
            {
                List<string> toReturn = new List<string>();

                dataConnectionDataContext dC = new dataConnectionDataContext();

                var query = (from sys in dC.GetTable<salesGoal>()
                             where (sys.storeNo == storeNumber) && (sys.FY == year) && (sys.tertial == tertial)
                             select sys);

                foreach (salesGoal sI in query)
                {
                    toReturn.Add(sI.holidayLimit);
                }

                dC.Dispose();

                return toReturn;
            }

        #endregion

        #region Resource Update

            public static int getCurrentGFPValues(int storeNo)
            {
                dataConnectionDataContext dc = new dataConnectionDataContext();

                int count = (from gfHFB in dc.GoodsFlowPlanning_HFBs
                             where (gfHFB.Loc == "STO" + storeNo)
                             select gfHFB).Count();

                return count;
            }

            public static int getCurrentASValues(int storeNo)
            {
                dataConnectionDataContext dc = new dataConnectionDataContext();

                int count = (from gfHFB in dc.ArticleStatus
                             where (gfHFB.Loc == "STO" + storeNo)
                             select gfHFB).Count();

                return count;
            }

            public static void truncateGFPDatabase(int storeNo, frmUpdate update)
            {
                dataConnectionDataContext dC = new dataConnectionDataContext();

                var query = from gfHFB in dC.GoodsFlowPlanning_HFBs
                            where (gfHFB.Loc == "STO" + storeNo)
                            select gfHFB;

                foreach (GoodsFlowPlanning_HFB GFP in query)
                {
                    dC.GoodsFlowPlanning_HFBs.DeleteOnSubmit(GFP);
                    dC.SubmitChanges();
                    update.progressBar1.Invoke(new Action(() => update.progressBar1.PerformStep()));
                }
                dC.Dispose();
            }

            public static void truncateASDatabase(int storeNo, frmUpdate update)
            {
                dataConnectionDataContext dC = new dataConnectionDataContext();

                var query = from aS in dC.ArticleStatus
                            where (aS.Loc == "STO" + storeNo)
                            select aS;

                foreach (ArticleStatus aS in query)
                {
                    dC.ArticleStatus.DeleteOnSubmit(aS);
                    update.progressBar2.Invoke(new Action(() => update.progressBar2.PerformStep()));
                    dC.SubmitChanges();
                }

                dC.Dispose();
            }

            public static void uploadNewGFPDatabase(frmUpdate update, string date, string loc, string sm, string hfb, string dt0pal, string dt0vol,
                string dt1pal, string dt1vol, string totpal, string totvol)
            {
                dataConnectionDataContext dA = new dataConnectionDataContext();

                GoodsFlowPlanning_HFB GFP = new GoodsFlowPlanning_HFB();

                GFP.Date = date;
                GFP.Loc = loc;
                GFP.SalesMethod = sm;
                GFP.HFB = hfb;
                GFP.DT_0_PAL = dt0pal;
                GFP.DT_0_VOL = dt0vol;
                GFP.DT_1_PAL = dt1pal;
                GFP.DT_1_VOL = dt1vol;
                GFP.TOT_PAL = totpal;
                GFP.TOT_VOL = totvol;

                dA.GoodsFlowPlanning_HFBs.InsertOnSubmit(GFP);
                dA.SubmitChanges();

                update.progressBar1.Invoke(new Action(() => update.progressBar1.PerformStep()));
            }

            public static void uploadNewASDatabase(frmUpdate update, string loc, string hfb, string item, string itemdesc, string recdate, string sm, string OuT, string ult, 
                string planstatus, string recqty, string recvol)
            {
                dataConnectionDataContext dA = new dataConnectionDataContext();

                ArticleStatus aS = new ArticleStatus();

                aS.Loc = loc;
                aS.HFB = hfb;
                aS.Item = item;
                aS.ItemDescr = itemdesc;
                aS.RecDate = recdate;
                aS.SalesMethod = sm;
                aS.OrderUnitType = OuT;
                aS.ULT = ult;
                aS.PlanStatus = planstatus;
                aS.Rec_QTY = recqty;
                aS.Rec_VOL_M3 = recvol;

                dA.ArticleStatus.InsertOnSubmit(aS);
                dA.SubmitChanges();

                update.progressBar2.Invoke(new Action(() => update.progressBar2.PerformStep()));
            }

        #endregion

        #region Employee Management

            public static List<employeeInformation> getCurrentEmployeeData(string storeNo)
            {
                dataConnectionDataContext dc = new dataConnectionDataContext();

                var result = (from aS in dc.GetTable<employeeInformation>()
                              where (aS.storeNo == storeNo)
                              select aS).ToList<employeeInformation>();

                var toReturn = result.OrderBy(c => c.workDate).ToList();

                return toReturn;
            }

            public static List<employeeInformation> getEmployeesPerDay(string storeNo, string date)
            {
                dataConnectionDataContext dc = new dataConnectionDataContext();

                var result = (from aS in dc.GetTable<employeeInformation>()
                              where (aS.storeNo == storeNo) && (aS.workDate == date)
                              select aS).ToList<employeeInformation>();

                var toReturn = result.OrderBy(c => c.empName).ToList();

                return toReturn;
            }

            public static List<string> getSingleEmployee(string empName, string date)
            {
                dataConnectionDataContext dc = new dataConnectionDataContext();

                List<string> toReturn = new List<string>();

                var result = (from aS in dc.GetTable<employeeInformation>()
                              where (aS.empName == empName) && (aS.workDate == date)
                              select aS).Single();

                toReturn.Add(result.startTime);
                toReturn.Add(result.endTime);

                return toReturn;
            }

            public static void uploadNewEmployeeInformation(string storeNo, string date, string name, string contHrs, string startTime, 
                string endTime, frmUserManagement user)
            {
                dataConnectionDataContext dA = new dataConnectionDataContext();

                employeeInformation eI = new employeeInformation();

                eI.storeNo = storeNo;
                eI.workDate = date;
                eI.empName = name;
                eI.contHours = contHrs;
                eI.startTime = startTime;
                eI.endTime = endTime;

                dA.employeeInformations.InsertOnSubmit(eI);
                dA.SubmitChanges();

                user.progressBar1.Invoke(new Action(() => user.progressBar1.PerformStep()));
            }

            public static void truncateEmployees(string storeNo, frmUserManagement user)
            {
                dataConnectionDataContext dC = new dataConnectionDataContext();

                var query = from eI in dC.employeeInformations
                            where (eI.storeNo == storeNo)
                            select eI;

                foreach (employeeInformation eI in query)
                {
                    dC.employeeInformations.DeleteOnSubmit(eI);
                    dC.SubmitChanges();
                    user.progressBar1.Invoke(new Action(() => user.progressBar1.PerformStep()));
                }
                dC.Dispose();
            }

            public static void truncateAssignment(string storeNo, string sm, string HFB)
            {
                dataConnectionDataContext dC = new dataConnectionDataContext();

                var query = from eI in dC.departmentAssignments
                            where (eI.storeNo == storeNo) && (eI.sm == sm) && (eI.department == HFB)
                            select eI;

                foreach (departmentAssignment eI in query)
                {
                    dC.departmentAssignments.DeleteOnSubmit(eI);
                    dC.SubmitChanges();
                }
                dC.Dispose();
            }

            public static void addAssignment(string name, string time, string department, string salesMethod, string storeNo, string date)
            {
                dataConnectionDataContext dA = new dataConnectionDataContext();

                departmentAssignment dAs = new departmentAssignment();

                dAs.employeeName = name;
                dAs.workTime = time;
                dAs.department = department;
                dAs.sm = salesMethod;
                dAs.storeNo = storeNo;
                dAs.workDate = date;

                dA.departmentAssignments.InsertOnSubmit(dAs);
                dA.SubmitChanges();
            }

            public static List<departmentAssignment> getWorkingEmployee(string sM, string HFB, string storeNo, string date)
            {
                dataConnectionDataContext dc = new dataConnectionDataContext();

                var result = (from aS in dc.GetTable<departmentAssignment>()
                              where (aS.storeNo == storeNo) && (aS.sm == sM) && (aS.department == HFB) && (aS.workDate == date)
                              select aS).ToList<departmentAssignment>();

                return result;
            }

            public static List<departmentAssignment> getWorkingEmployee(string storeNo, string date)
            {
                dataConnectionDataContext dc = new dataConnectionDataContext();

                var result = (from aS in dc.GetTable<departmentAssignment>()
                              where (aS.storeNo == storeNo) && (aS.workDate == date)
                              select aS).ToList<departmentAssignment>();

                return result;
            }

            public static List<departmentAssignment> getAllWorkingEmployees(string storeNo, string date)
            {
                dataConnectionDataContext dc = new dataConnectionDataContext();

                var result = (from aS in dc.GetTable<departmentAssignment>()
                              where (aS.storeNo == storeNo) && (aS.workDate == date)
                              orderby aS.employeeName ascending
                              select aS).ToList<departmentAssignment>();

                return result;
            }

            public static void deleteDepartmentSchedule(string storeNo, string name, string time, string date)
            {
                dataConnectionDataContext dC = new dataConnectionDataContext();

                var query = from eI in dC.departmentAssignments
                            where (eI.storeNo == storeNo) && (eI.employeeName == name) && (eI.workDate == date) && (eI.workTime == time)
                            select eI;

                foreach (departmentAssignment dA in query)
                {
                    dC.departmentAssignments.DeleteOnSubmit(dA);
                    dC.SubmitChanges();
                }

                dC.Dispose();
            }

        #endregion


            public static bool checkForDatabase()
            {
                try
                {
                    dataConnectionDataContext dc = new dataConnectionDataContext();

                    var result = (from aS in dc.GetTable<employeeInformation>()
                                  where (aS.storeNo == "261")
                                  select aS).ToList<employeeInformation>();

                    var toReturn = result.OrderBy(c => c.workDate).ToList();

                    return true;
                }
                catch (Exception e)
                {
                    return false;
                }
            }
    }        
}
